﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

using dotNails.Util;
using dotNails.CodeGen.Models;
using dotNails.CodeGen.Util;
using System.Xml.Linq;
using System.Collections;
using EnvDTE;
using System.Text.RegularExpressions;

namespace dotNails.UI {
	/// <summary>
	/// Interaction logic for DBManager.xaml
	/// </summary>
	public partial class SyncTables : UserControl, IChildUserControl {
		private DbmlData _dbml;
		private IUserControlParent _parent;
		private bool _settingSelectAll = false;

		public SyncTables(IUserControlParent parent, DbmlData dbml) {
			InitializeComponent();
			_parent = parent;
			_dbml = dbml;
			TitleBar.Text = "Sync " + _dbml.Name;
		}

		//private void Window_Loaded(object sender, RoutedEventArgs e) {
		public void Display() {
			// List the tables from the dbml
			List<dotNails.CodeGen.Models.Table> tables = _dbml.DB.Tables;
			Tables.ItemsSource = tables;
			Tables.SelectedIndex = -1;
		}

		void btnBack_Click(object sender, RoutedEventArgs e) {
			// Find all dbmls in the solution
			List<DbmlData> dbmls = AddInUtil.GetAllDBMLsInSolution(_parent.DTE);

			UserControl previousUserControl = new DatabaseList(_parent, dbmls);
			_parent.LoadUserControl(previousUserControl);
		}

		void btnSync_Click(object sender, RoutedEventArgs e) {
			try {
				// Get smo objects, so we can read all smo info for the affected tables to get any changes.
				SqlConnection conn = new SqlConnection(_dbml.DB.Connection.ConnectionString);
				Server server = new Server(new ServerConnection(conn));
				Database smoDb = SmoUtil.GetSmoDatabase(server, _dbml.DB.Name);

				// Split the dbml tables into two lists - tables to be synced and tables not being synced
				List<dotNails.CodeGen.Models.Table> syncedTables = new List<dotNails.CodeGen.Models.Table>();
				List<dotNails.CodeGen.Models.Table> nonSyncedTables = new List<dotNails.CodeGen.Models.Table>();
				List<dotNails.CodeGen.Models.Table> tablesToSync = new List<dotNails.CodeGen.Models.Table>();

				foreach (dotNails.CodeGen.Models.Table dbmlTable in Tables.SelectedItems) {
					// Sync the selected tables with the db to get the most current info
					dotNails.CodeGen.Models.Table tableToSync = SmoUtil.SyncDbmlTableWithDatabase(smoDb, dbmlTable);
					syncedTables.Add(tableToSync);
				}

				foreach (dotNails.CodeGen.Models.Table dbmlTable in _dbml.DB.Tables) {
					if (syncedTables.Find(t => t.Name == dbmlTable.Name) == null) {
						nonSyncedTables.Add(dbmlTable);
					}
				}

				if (nonSyncedTables.Count > 0) {
					// Find all tables within the nonSyncedTables that are related to the synced tables - we're going to sync these too (just to one level, nothing recursive here)
					for (int i = 0; i < syncedTables.Count; i++) {
						//foreach (dotNails.CodeGen.Models.Table tableToSync in syncedTables) {
						foreach (TableRelationship relationship in syncedTables[i].TableRelationships) {
							dotNails.CodeGen.Models.Table nonSyncedTable = null;
							TableRelationship newRelationship = null;

							if (relationship.IsReferencingTable) {
								// Check to see if the referenced table is within the non-synced table list
								string fullTableName = DbmlUtil.BuildDbmlTableName(relationship.ReferencedTableSchema, relationship.ReferencedTable);
								nonSyncedTable = nonSyncedTables.Find(t => t.Name == fullTableName);

								if (nonSyncedTable != null) {
									// See if an association already exists for this relationship in this table
									Association existingAssociation = nonSyncedTable.Type.Associations.Find(a => a.Name == DbmlUtil.GetAssociationName(relationship));

									if (existingAssociation != null) {
										nonSyncedTable.Type.Associations.Remove(existingAssociation);
									}

									newRelationship = new TableRelationship();
									nonSyncedTable.TableRelationships.Add(newRelationship);

									// Set the relationship data on the related dbml table
									DbmlUtil.SetReferencedRelationshipFromReferencingRelationship(newRelationship, relationship, nonSyncedTable.Type.Name);
								}
							}
							else {
								// Check to see if a referencing table is within the old dbml
								string fullTableName = DbmlUtil.BuildDbmlTableName(relationship.ReferencingTableSchema, relationship.ReferencingTable);
								nonSyncedTable = nonSyncedTables.Find(t => t.Name == fullTableName);

								if (nonSyncedTable != null) {
									if (nonSyncedTable.TableRelationships == null) {
										nonSyncedTable.TableRelationships = new List<TableRelationship>();
									}

									// See if an association already exists for this relationship in this table
									Association existingAssociation = nonSyncedTable.Type.Associations.Find(a => a.Name == DbmlUtil.GetAssociationName(relationship));


									if (existingAssociation != null) {
										nonSyncedTable.Type.Associations.Remove(existingAssociation);
									}

									newRelationship = new TableRelationship();
									nonSyncedTable.TableRelationships.Add(newRelationship);

									// Set the relationship data on the related dbml table
									DbmlUtil.SetReferencingRelationshipFromReferencedRelationship(newRelationship, relationship, nonSyncedTable.Type.Name);
								}
							}

							if (nonSyncedTable != null) {
								dotNails.CodeGen.Models.Table table = tablesToSync.Find(t => t.Name == nonSyncedTable.Name);
								// Don't sync if it is already in the list
								if (table == null) {
									tablesToSync.Add(nonSyncedTable);
								}
							}
						}
					}

					for (int i = 0; i < tablesToSync.Count; i++) {
						tablesToSync[i] = SmoUtil.SyncDbmlTableWithDatabase(smoDb, tablesToSync[i]);
						syncedTables.Add(tablesToSync[i]);
					}

				}

				// Pair relationships among all the synced tables
				DbmlUtil.PairTableRelationships(syncedTables);

				// Maintain the associations that are unrelated to the tables being added
				foreach (dotNails.CodeGen.Models.Table syncedTable in syncedTables) {
					dotNails.CodeGen.Models.Table dbmlTable = _dbml.DB.Tables.Find(t => t.Name == syncedTable.Name);

					// New
					// Get the names of all the associations that will be created from the paired relationships
					List<Association> newAssociations = new List<Association>();
					foreach (TableRelationship rel in syncedTable.TableRelationships) {
						if (rel.Paired) {
							bool isDupe = false;
							string name = DbmlUtil.CreateAssociationName(rel, newAssociations, ref isDupe);
							newAssociations.Add(new Association { Name = name });
						}
					}

					foreach (Association association in dbmlTable.Type.Associations) {
						// If the last character of the association name is an integer, ignore it for this comparison (we don't want multiple foreign keys to the same table causing problems here).
						//  **If we can have two associations to the same table and that table is unrelated to the tables being synced, then we'll have a problem here (only one assoc will be maintained). Is this possible?
						//    TODO: Try to sync a table that has User as a relationship, but User has two FK's to another table (make sure that other table isn't involved in the sync).  Test that.
						//string associationName = association.Name;
						//// See if last character is a number
						//Match match = Regex.Match(associationName.Substring(associationName.Length - 1), @"\d");

						//if (!string.IsNullOrEmpty(match.Value)) {
						//    associationName = associationName.Substring(0, associationName.Length - 1);
						//}

						// Don't add any association that will be created from a paired relationship.  Let those be overwritten.
						//TableRelationship nonPairedRelationship = syncedTable.TableRelationships.Find(r => r.Paired && DbmlUtil.GetAssociationName(r) == associationName);
						
						// Don't add any association that will be created due to the sync.  Let those be overwritten by the sync.
						Association associationAboutToBeCreated = newAssociations.Find(a => a.Name == association.Name);
						if (associationAboutToBeCreated == null) {
							// Copy any association over from the existing dbml that isn't affected by a paired relationship (otherwise it will be deleted)
							syncedTable.Type.Associations.Add(association);
						}
					}
				}

				// Populate the associations for all the synced tables
				DbmlUtil.PopulateAssociations(syncedTables);

				// Create a new DbmlData construct to merge with the old (just need the synced tables to be in there)
				DbmlData newDbml = new DbmlData { DB = new DataBase { Tables = new List<dotNails.CodeGen.Models.Table>() } };
				foreach (dotNails.CodeGen.Models.Table table in syncedTables) {
					newDbml.DB.Tables.Add(table);
				}

				//  Replace tables in old dbml with same name, but leave the rest alone (rewrite them all to xml)
				DbmlUtil.MergeNewDbmlWithOld(newDbml, _dbml);
				XElement xml = _dbml.SerializeToXml();

				// Write out the dbml
				xml.Save(_dbml.FilePath);

				// Execute the custom tool for the dbml 
				_parent.RunCustomToolOnFile(_dbml.FilePath);

			}
			catch (Exception ex) {
				LogUtil.EventLogMessage(ex.Message, ex.InnerException, ex.StackTrace); 
			}

			//_parent.Close();
			Display();
		}

		private void chkSelectAll_Click(object sender, RoutedEventArgs e) {
			// The checkbox will immediately change its state when clicked, so coming in checked, means it was just checked
			if (!chkSelectAll.IsChecked.Value) {
				// Checkbox was deselected, so unselect all items in listbox
				Tables.UnselectAll();
			}
			else {
				_settingSelectAll = true;
				Tables.SelectAll();
				_settingSelectAll = false;
			}
		}

		private void Tables_SelectionChanged(object sender, RoutedEventArgs e) {
			if (!_settingSelectAll && chkSelectAll.IsChecked.Value) {
				chkSelectAll.IsChecked = false;
			}
		}
	}
}